package humanMirror.model.questionLibrary;

import java.sql.*;
import java.util.ArrayList;

public class SqlWorker implements SqlWorkerInterfase{
    private Connection connection;
    private PreparedStatement ps;
    private ResultSet rs;
    private int answerNumber;
    String path = "jdbc:sqlite:HM.sqlite";
//    private String path="jdbc:sqlite:c:\\HM.sqlite";

    public SqlWorker()  {
        try {
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager
                    .getConnection(path);
            answerNumber = getCountOf("answers");
        } catch (ClassNotFoundException e) {
            System.out.print("Can't connect to database");
            System.exit(0);
        }
        catch (SQLException e) {
            System.out.print("Can't connect to database " + e.toString());
            System.exit(0);
        }

    }

    public SqlWorker(String base)
            throws SQLException {
        connection = DriverManager.getConnection(base);
        answerNumber = getCountOf("answers");
    }

    public String[] getAnswers(int numberOfQuestion)// !+1
            throws SQLDataException, SQLException {
        return getStringArrayResult(numberOfQuestion +1,
                "SELECT answer FROM answers WHERE Question=?", "Answer");
    }

    public String getQuestion(int numberOfQuestion) throws SQLDataException,
            SQLException {
        return getStringResult(numberOfQuestion + 1,
                "SELECT Question FROM questions WHERE Id=?");
    }

    public int getCountOfQuestions() throws SQLException {
        return getCountOf("questions");
    }

    public int currentEffect(int organ, int param, int questionNumber,
                             int numberOfAnswer) throws SQLException {
        return getIntResult(queryMaker(param, organ, questionNumber,
                numberOfAnswer));
    }

    public  static  boolean checkRegistration(String login, String password) throws SQLException {
        SqlWorker worker=new SqlWorker();
        SQLException ex=null;
        int index=-1;
        try {
            index= worker.getIntResult("SELECT id FROM USERS WHERE LOGIN='" + login +
                    "' AND PASSWORD='" + password + "'") ;
        } catch (SQLException e) {
            if (e.getMessage()=="This data does not exist, or is not an int"){
                ex=new SQLDataException("User not found");
            } else ex=e;
        } finally {
            worker.disconnect();
            if(ex!=null) throw ex;
        }
        if(index != -1) return true;
        return false;

    }

    private boolean registerNewUser(String login, String password) throws SQLException {
        execute("SELECT * FROM USERS WHERE LOGIN='" + login + "'");
       if (rs.next()) {
           throw new SQLException("User with this login already exist");            
        } else {
            ps = connection.prepareStatement("INSERT INTO Users (Login,Password) VALUES (?1,?2)");
            ps.setString(1, login);
            ps.setString(2, password);
            ps.execute(); 
       }return true; 
    }
   
    public static boolean registerUser(String login, String password) throws SQLException {
        boolean result=false;
        SQLException ex=null;
        SqlWorker worker=new SqlWorker();
        try {
            result =worker.registerNewUser(login,password);
        } catch (SQLException e) {
           ex=e;
        }
        finally {
            worker.disconnect();
            if(ex!=null) throw ex;
        }
        return result;
    }
    int getIntResult(int param, String query) throws SQLException {
        execute(param, query);
        return getIntResult();
    }

    int getIntResult(String query) throws SQLException {
        execute(query);
        int result = 0;
        
        if (rs.next()) {
            result = rs.getInt(1);
        } else
            throw new SQLDataException(
                    "This data does not exist, or is not an int");
        // rs.close();
        return result;
    }

    String getStringResult(int param, String query) throws SQLDataException,
            SQLException {
        execute(param, query);
        return getStringResult();
    }

    String[] getStringArrayResult(int param, String query, String column)
            throws SQLDataException, SQLException {
        execute(param, query);
        return getStringArrayResult(column);
    }

    private void execute(String query) throws SQLException {
        ps = connection.prepareStatement(query);
        rs = ps.executeQuery();

        // ps.close();
    }

    private void execute(int param, String query) throws SQLException {
        ps = connection.prepareStatement(query);
        ps.setInt(1, param);
        rs = ps.executeQuery();

        // ps.close();
    }

    private String getStringResult() throws SQLDataException, SQLException {
        String result = "";
        if (rs.next()) {
            result = rs.getString(1);
        } else
            throw new SQLDataException(
                    "This data does not exist, or is not a string");
        // rs.close();
        return result;
    }

    String[] getStringArrayResult(String column) throws SQLDataException,
            SQLException {
        ArrayList<String> answers = new ArrayList<String>();
        while (rs.next()) {
            answers.add(rs.getString(column));
        }
        String[] result = new String[answers.size()];
        answers.toArray(result);
        // rs.close();
        return result;
    }

    private int getIntResult() throws SQLDataException, SQLException {
        int result = 0;
        if (rs.next()) {
            result = rs.getInt(1);
        } else
            throw new SQLDataException(
                    "This data does not exist, or is not an int");
        // rs.close();
        return result;
    }

    private int getCountOf(String tableName) throws SQLException {
        execute("SELECT * FROM " + tableName);
        int result = 0;
        while (rs.next()) {
            result = rs.getInt("Id");
        }
        return result;
    }

    public void disconnect() throws SQLException {
        ps.close();
        rs.close();

        connection.close();

    }



    private String chooseOrgan(int organ) {
        String organName = "Brain_";
        switch (organ) {
            case 0:
                organName = "Brain_";
                break;
            case 1:
                organName = "Liver_";
                break;
            case 2:
                organName = "Stomack_";
                break;
            case 3:
                organName = "Lungs_";
                break;
            case 4:
                organName = "Heart_";
                break;
        }
        return organName;
    }

    private String queryMaker(int param, int organ, int questionNumber,
                              int answer) {
        StringBuilder query = new StringBuilder();
        query.append("select ");
        query.append(chooseOrgan(organ));
        query.append(param);
        query.append(" from answers where question=");
        query.append(questionNumber + 1);
        query.append(" and answernum=");
        query.append(answer + 1);
        return query.toString();

    }
}
